﻿Imports MySql.Data.MySqlClient
Public Class frmedit_gate
    Dim mysql As MySqlConnection = main_form.mysqlconection
    Dim mySqlCommand As New MySqlCommand
    Dim mySqlAdaptor As New MySqlDataAdapter
    Dim mySqlReader As MySqlDataReader
    Dim idcontainer As String
    Dim idbooking As String
    Dim ctnstat As String
    Dim idcontainerberfor As String


    Dim textDate As String
    Dim stringDate() As String
    Dim stringhour() As String
    Dim sumtxt As String
    Dim sumint As Double


    Private Sub frmedit_gate_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
        System.Threading.Thread.CurrentThread.CurrentUICulture = System.Threading.Thread.CurrentThread.CurrentCulture
        mysql.Close()
        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If
        mySqlCommand.CommandText = "Select * from borrow join booking  on  borrow.BOOKID = booking.BOOKINGID join ctnmain on borrow.CTNID = ctnmain.CTNMAINID where idborrow ='" & frmview_voyage.idborrow & "' ;"
        ' mySqlCommand.CommandText = 
        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand

        Try
            mySqlReader = mySqlCommand.ExecuteReader

            While (mySqlReader.Read())
                idcontainerberfor = mySqlReader("CTNID")
                idcontainer = mySqlReader("CTNID")
                txt_name_car.Text = mySqlReader("COMNAME")
                txt_no_car.Text = mySqlReader("CARID")
                DateTimePicker4.Text = mySqlReader("TIMEHHMM")
                DateTimePicker2.Text = mySqlReader("TIMEDATE")
                txt_insurance.Text = mySqlReader("INSURANCE")
                txt_customer.Text = mySqlReader("BCONSINAME")
                If mySqlReader("TIMEHHMMIN") Is DBNull.Value Then
                Else
                    DateTimePicker1.Text = mySqlReader("TIMEHHMMIN")

                End If
                If mySqlReader("TIMEDATEIN") Is DBNull.Value Then
                Else
                    DateTimePicker3.Text = mySqlReader("TIMEDATEIN")

                End If

                txt_voyage.Text = Format(CInt(mySqlReader("BVOYAGE")), "000")
                TextBox2.Text = Format(CInt(mySqlReader("BVOYAGE")), "000")
                txt_booking_no.Text = mySqlReader("BNO")
                txt_container.Text = mySqlReader("CTNSTRING")
                If mySqlReader("CTNSEALID") Is DBNull.Value Then
                Else
                    txt_sealid.Text = mySqlReader("CTNSEALID")
                End If
                If mySqlReader("CTNSEALID") Is DBNull.Value Then
                Else
                    TextBox3.Text = mySqlReader("CTNWEIGHT")
                End If
                If mySqlReader("CTNINS") Is DBNull.Value Then
                Else
                    txt_insurance.Text = mySqlReader("CTNINS")
                End If

         


                TextBox1.Text = mySqlReader("BCTNTYPETHAI")
                TextBox4.Text = mySqlReader("BCTNTYPEPAE")
            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()


      
        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If
        mySqlCommand.CommandText = "Select * from ctnmain where CTNSTAT = '1' ;"
        ' mySqlCommand.CommandText = 
        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand

        Try
            mySqlReader = mySqlCommand.ExecuteReader
            ListView1.Items.Clear()
            While (mySqlReader.Read())

                With ListView1.Items.Add(mySqlReader("CTNSTRING"))


                    If mySqlReader("CTNAGENT") Is DBNull.Value Then
                        .SubItems.Add("-")
                    Else

                        .SubItems.Add(mySqlReader("CTNAGENT"))
                    End If
                    If mySqlReader("CTNSIZE") Is DBNull.Value Then
                        .SubItems.Add("-")
                    Else

                        .SubItems.Add(mySqlReader("CTNSIZE"))
                    End If


                    .SubItems.Add(Format(mySqlReader("CTNVOYN"), "000"))

                    .SubItems.Add(mySqlReader("CTNMAINID"))
                End With





            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()
    End Sub

    Private Sub ButtonX3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonX3.Click
     
     


        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If
        Dim commandText3 As String
        commandText3 = "UPDATE ctnmain SET CTNSTAT = '1',CTNSEALID = '',CTNWEIGHT ='0' where CTNMAINID = '" & idcontainer & "'; "


        Try
            mySqlCommand.CommandText = commandText3
            mySqlCommand.CommandType = CommandType.Text
            mySqlCommand.Connection = mysql

            mySqlCommand.ExecuteNonQuery()

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()


        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If

        commandText3 = "UPDATE ctnmain SET CTNSTRING  = '" & txt_container.Text & "',CTNWEIGHT ='" & TextBox3.Text & "',CTNSEALID = '" & txt_sealid.Text & "',CTNVOYS = '" & frmsearch_voyage1.idvoyage & "' ,CTNINS ='" & txt_insurance.Text & "'where CTNMAINID = '" & idcontainerberfor & "'; "


        Try
            mySqlCommand.CommandText = commandText3
            mySqlCommand.CommandType = CommandType.Text
            mySqlCommand.Connection = mysql

            mySqlCommand.ExecuteNonQuery()

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()


        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If

        commandText3 = "UPDATE borrow SET CTNID  = '" & idcontainer & "',COMNAME = '" & txt_name_car.Text & "',CARID = '" & txt_no_car.Text & "',TIMEHHMM ='" & DateTimePicker4.Text & "',TIMEDATE ='" & DateTimePicker2.Text & "',TIMEDATEIN ='" & DateTimePicker3.Text & "',TIMEHHMMIN ='" & DateTimePicker1.Text & "',INSURANCE ='" & txt_insurance.Text & "' where idborrow = '" & frmview_voyage.idborrow & "'; "


        Try
            mySqlCommand.CommandText = commandText3
            mySqlCommand.CommandType = CommandType.Text
            mySqlCommand.Connection = mysql

            mySqlCommand.ExecuteNonQuery()

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()
        MsgBox("แก้ไขข้อเรียบร้อย")
        Dim cf As New frmview_voyage

        cf.MdiParent = Me.MdiParent
        Me.Close()
        cf.Dock = DockStyle.Fill
        cf.Show()







    End Sub

    Private Sub ListView1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListView1.Click
        Dim respone As Object
        respone = MsgBox("ท่านต้องการเปลี่ยนตู้คอนเทนเนอร์ใช่หรือไม่", MsgBoxStyle.OkCancel + MsgBoxStyle.Information, "Warning Messsage")
        If respone = 1 Then
            idcontainer = ListView1.SelectedItems(0).SubItems(4).Text
            txt_container.Text = ListView1.SelectedItems(0).SubItems(0).Text
        End If


    End Sub

    Private Sub ButtonX1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonX1.Click
        Dim cf As New frmview_voyage

        cf.MdiParent = Me.MdiParent
        Me.Close()
        cf.Dock = DockStyle.Fill
        cf.Show()


    End Sub

    Private Sub ButtonX2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonX2.Click
        functionCal()

    End Sub
    Public Sub functionCal()
        ListView1.Items.Clear()
        mysql.Close()
        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If
        mySqlCommand.CommandText = "Select * from  ctnmain   where  CTNSTAT = '1' and ( CTNSTRING like '%" & txtsearch.Text & "%' or CTNVOYN like '%" & txtsearch.Text & "%' or CTNAGENT like '%" & txtsearch.Text & "%');"        ' mySqlCommand.CommandText = 
        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand

        Try
            mySqlReader = mySqlCommand.ExecuteReader
            ListView1.Items.Clear()
            While (mySqlReader.Read())

                With ListView1.Items.Add(mySqlReader("CTNSTRING"))


                    If mySqlReader("CTNAGENT") Is DBNull.Value Then
                        .SubItems.Add("-")
                    Else

                        .SubItems.Add(mySqlReader("CTNAGENT"))
                    End If
                    If mySqlReader("CTNSIZE") Is DBNull.Value Then
                        .SubItems.Add("-")
                    Else

                        .SubItems.Add(mySqlReader("CTNSIZE"))
                    End If


                    .SubItems.Add(Format(mySqlReader("CTNVOYN"), "000"))

                    .SubItems.Add(mySqlReader("CTNMAINID"))
                End With


            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()




    End Sub

    Private Sub txtsearch_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtsearch.KeyDown
        If e.KeyCode = Keys.Enter Then
            functionCal()
        End If
    End Sub

End Class